package cn.tang.crm.service.action;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;

import cn.tang.crm.comm.TypeCache;
import cn.tang.crm.comm.UIMessage;
import cn.tang.crm.comm.Utils;

public class AdminActionService extends ActionService {
	private static final Logger logger = Logger.getLogger(AdminActionService.class);

	public String addUser(String username, String password, String roleID) {
		if (userNameExist(username)) {
			return UIMessage.USER_NAME_EXIST;
		}

		String sql = "insert into user values(uuid(),'" + username + "','" + Utils.md5(password) + "','" + roleID + "',0)";
		logger.debug(sql);
		String ret = "";
		int i = connPool.executeUpdate(sql);
		if (i == 1) {
			ret = UIMessage.ADD_USER_SUCCESS;
		} else {
			ret = UIMessage.ADD_USER_FAILURE;
		}
		return ret;
	}

	private boolean clientTypeNameExist(String clientTypeName) {
		String sql = "select id from clientType where clientTypeName='" + clientTypeName + "'";
		logger.debug(sql);
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return false;
	}

	private boolean roleNameExist(String roleName) {
		String sql = "select id from userRole where roleName='" + roleName + "'";
		logger.debug(sql);
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return false;
	}

	private boolean clientClassNameExist(String clientClassName) {
		String sql = "select id from clientClass where clientClassName='" + clientClassName + "'";
		logger.debug(sql);
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return false;
	}

	private boolean userNameExist(String username) {
		String sql = "select id from user where username='" + username + "'";
		logger.debug(sql);
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return false;
	}

	public String login(String username, String password, HttpServletRequest req) {
		String sql = "select * from user where username='" + username + "' and password='" + Utils.md5(password) + "'";
		logger.debug(sql);
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				String roleID = rs.getString("roleID");
				req.getSession().setAttribute("username", username);
				req.getSession().setAttribute("roleID", roleID);
				if (roleID.equals("af1c664a-e0f4-4f5b-80e5-710867b2e8ba")) {
					return "admin";
				} else if (roleID.equals("30547d61-9b09-4771-a325-90a4219d6369")) {
					return "user";
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return UIMessage.NAME_PWD_ERROR;
	}

	public String getClientTypeOption() {
		String sql = "select * from clientType";
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("id");
				String clentTypeName = rs.getString("clentTypeName");
				ret += "<option value='" + id + "'>" + clentTypeName + "</option>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String getClentClassOption() {
		String sql = "select * from clientClass";
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("id");
				String clientClassName = rs.getString("clientClassName");
				ret += "<option value='" + id + "'>" + clientClassName + "</option>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String getRoleOption() {
		String sql = "select * from userRole";
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("id");
				String roleName = rs.getString("roleName");
				ret += "<option value='" + id + "'>" + roleName + "</option>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String listAllClientType() {
		String sql = "select * from clientType";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int index = 0;
			while (rs.next()) {
				index++;
				if (index % 2 == 0) {
					ret += "<tr class='alt'>";
				} else {
					ret += "<tr>";
				}
				String id = rs.getString("id");
				ret += "<td>" + rs.getString("clientTypeName") + "</td>";
				ret += "<td>" + rs.getString("clientTypeDescription") + "</td>";
				ret += "<td><span class='spanLinkClass' onclick='deleteClientType(\"" + id + "\")'>delete</span></td>";
				ret += "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String listAllClientClass() {
		String sql = "select * from clientClass";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int index = 0;
			while (rs.next()) {
				index++;
				if (index % 2 == 0) {
					ret += "<tr class='alt'>";
				} else {
					ret += "<tr>";
				}
				String id = rs.getString("id");
				ret += "<td>" + rs.getString("clientClassName") + "</td>";
				ret += "<td>" + rs.getLong("clientClassMiniTrade") + "</td>";
				ret += "<td>" + rs.getLong("clientClassMaxTrade") + "</td>";
				ret += "<td>" + rs.getString("clientClassDescription") + "</td>";
				ret += "<td><span class='spanLinkClass' onclick='deleteClientClass(\"" + id + "\")'>delete</span></td>";
				ret += "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String listAllRole() {
		String sql = "select * from userRole";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int index = 0;
			while (rs.next()) {
				index++;
				if (index % 2 == 0) {
					ret += "<tr class='alt'>";
				} else {
					ret += "<tr>";
				}
				ret += "<td>" + rs.getString("roleName") + "</td>";
				ret += "<td>" + rs.getString("roleDescription") + "</td>";
				ret += "<td>&nbsp;</td>";
				ret += "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String listAllUser() {
		String sql = "select a.id,username,roleName,lastLoginTime from user a,userRole b where a.roleId=b.id;";
		logger.debug(sql);
		String ret = "";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int index = 0;
			while (rs.next()) {
				index++;
				if (index % 2 == 0) {
					ret += "<tr class='alt'>";
				} else {
					ret += "<tr>";
				}
				String userName = rs.getString("username");
				String id = rs.getString("id");
				ret += "<td>" + userName + "</td>";
				ret += "<td>" + rs.getString("roleName") + "</td>";
				ret += "<td>" + Utils.formatTime(rs.getLong("lastLoginTime")) + "</td>";
				ret += "<td><span class='spanLinkClass' onclick='resetPwd(\"" + id + "\")'>reset password</span>&nbsp;&nbsp;";
				ret += "<span class='spanLinkClass' onclick='getUserInfo(\"" + id + "\")'>update</span>&nbsp;&nbsp;";
				ret += "<span class='spanLinkClass' onclick='deleteUser(\"" + id + "\")'>delete</span></td>";
				ret += "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return ret;
	}

	public String updateUser(String id, String roleID) {
		String sql = "update user set roleID='" + roleID + "' where id='" + id + "'";
		logger.debug(sql);
		String ret = "";
		if (connPool.executeUpdate(sql) == 1) {
			ret = UIMessage.UPDATE_USER_SUCCESS;
		} else {
			ret = UIMessage.UPDATE_USER_FAILURE;
		}
		return ret;
	}

	public String resetPwd(String id) {
		String defaultPwd = "111111";
		String sql = "update user set password='" + Utils.md5(defaultPwd) + "' where id='" + id + "'";
		logger.debug(sql);
		String ret = "";
		if (connPool.executeUpdate(sql) == 1) {
			ret = UIMessage.RESET_PWD_SUCCESS;
		} else {
			ret = UIMessage.RESET_PWD_FAILURE;
		}
		return ret;
	}

	public String deleteUser(String id) {
		String sql = "delete from user where id='" + id + "'";
		logger.debug(sql);
		String ret = "";
		if (connPool.executeUpdate(sql) == 1) {
			ret = UIMessage.DELETE_USER_SUCCESS;
		} else {
			ret = UIMessage.DELETE_USER_FAILURE;
		}
		return ret;
	}

	public Map<String, String> getUserInfo(String id) {
		String sql = "select * from user where id='" + id + "'";
		Map<String, String> retMap = new HashMap<String, String>();
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = connPool.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				retMap.put("id", rs.getString("id"));
				retMap.put("username", rs.getString("username"));
				String roleID = rs.getString("roleID");
				Map<String, String> roleMap = TypeCache.instance.roleCacheMap;
				Set<Entry<String, String>> entrySet = roleMap.entrySet();
				String optionHtml = "";
				for (Entry<String, String> entry : entrySet) {
					String tmpID = entry.getKey();
					String tmpValue = entry.getValue();
					if (tmpID.equals(roleID)) {
						optionHtml += "<option value='" + tmpID + "' selected='selected'>" + tmpValue + "</option>";
					} else {
						optionHtml += "<option value='" + tmpID + "'>" + tmpValue + "</option>";
					}
				}
				retMap.put("optionHtml", optionHtml);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connPool.returnResource(conn, stmt);
		}
		return retMap;
	}

	public String addClientType(String clientTypeName, String clientTypeDescription) {
		if (clientTypeNameExist(clientTypeName)) {
			return UIMessage.CLIENT_TYPE_EXIST;
		}
		String sql = "insert into clientType values(uuid(),'" + clientTypeName + "','" + clientTypeDescription + "')";
		logger.debug(sql);
		String ret = "";
		int i = connPool.executeUpdate(sql);
		if (i == 1) {
			ret = UIMessage.ADD_CLIENT_TYPE_SUCCESS;
		} else {
			ret = UIMessage.ADD_CLIENT_TYPE_FAILURE;
		}
		return ret;
	}

	public String addClientClass(String clientClassName, String clientClassMiniTrade, String clientClassMaxTrade, String clientClassDescription) {
		if (clientClassNameExist(clientClassName)) {
			return UIMessage.CLIENT_CLASS_EXIST;
		}
		String sql = "insert into clientClass values(uuid(),'" + clientClassName + "'," + clientClassMiniTrade + "," + clientClassMaxTrade + ",'"
				+ clientClassDescription + "')";
		logger.debug(sql);
		String ret = "";
		int i = connPool.executeUpdate(sql);
		if (i == 1) {
			ret = UIMessage.ADD_CLIENT_TYPE_SUCCESS;
		} else {
			ret = UIMessage.ADD_CLIENT_TYPE_FAILURE;
		}
		return ret;
	}

	public String addUserRole(String roleName, String roleDescription) {
		if (roleNameExist(roleName)) {
			return UIMessage.CLIENT_CLASS_EXIST;
		}
		String sql = "insert into userRole values(uuid(),'" + roleName + "','" + roleDescription + "')";
		logger.debug(sql);
		String ret = "";
		int i = connPool.executeUpdate(sql);
		if (i == 1) {
			ret = UIMessage.ADD_ROLE_SUCCESS;
		} else {
			ret = UIMessage.ADD_ROLE_FAILURE;
		}
		return ret;
	}

}
